Release 10.1A: OpenEdge Data Management:
SQL Development

Java snippet

The core of the stored procedure is the Java snippet. The snippet contains a sequence of Java statements. When it processes a CREATE PROCEDURE statement, OpenEdge SQL adds header and footer “wrapper” code to the Java snippet. This wrapper code:

Declares a class with the name username_procname_SP, where username is the user name of the database connection that issued the CREATE PROCEDURE statement and procname is the name supplied in the CREATE PROCEDURE statement.
Declares a method within that class that includes the Java snippet. When an application calls the stored procedure, the SQL engine calls the Java virtual machine to invoke the method of the username_procname_SP class.

Structure of stored procedures

There are two parts to any stored procedure:

The procedure specification provides the name of the procedure and can include other optional clauses:
Parameter declarations.
Procedure result set declarations.
Import clauses.
The procedure body contains the Java code that executes when an application invokes the procedure.

A simple stored procedure requires the procedure name in the specification and a statement requiring no parameters in the body. Example 9–2 assumes the existence of a table named HellowWorld, and inserts a quoted string into that table.

Example 9–2: Creating a stored procedure

CREATE PROCEDURE HelloWorld () BEGIN SQLIStatement Insert_HelloWorld = new SQLIStatement ( "INSERT INTO HelloWorld(fld1) values (’Hello World!’)"); Insert_HelloWorld.execute(); END;

Subsequently, you can execute the procedure shown in Example 9–3.

Example 9–3: Executing a stored procedure

SQLExplorer> CREATE TABLE helloworld (fld1 CHAR(100)); SQLExplorer> CALL HelloWorld(); 0 records returned SQLExplorer> SELECT * FROM helloworld; FLD1 ---- Hello World! 1 record selected

The procedure specification can also contain other clauses.

Parameter declarations specify the names and types of parameters that the calling application will pass and receive from the procedure. Parameters can be input, output, or both.

The procedure result set declaration details the names and types of fields in a result set the procedure generates. The result set is a set of rows that contain data generated by the procedure. If a procedure retrieves rows from a database table, for instance, it can store the rows in a result set for access by applications and other procedures. The names specified in the result-set declaration are not used within the stored procedure body. Instead, methods of the OpenEdge SQL Java classes refer to fields in the result set by ordinal number, not by name.

The import clause specifies which packages the procedure needs from the Java core API. By default, the Java compiler imports the java.lang package. The IMPORT clause must list any other packages the procedure uses. OpenEdge SQL automatically imports the packages it requires.

Example 9–4 shows a more complex procedure specification that contains these elements.

Example 9–4: CREATE PROCEDURE statement

CREATE PROCEDURE new_sal ( IN deptnum INTEGER, IN pct_incr INTEGER ) RESULT ( empname CHAR (20), oldsal NUMERIC, newsal NUMERIC ) IMPORT import java.dbutils.SequenceType; BEGIN . . . END